//
//  SQLite3Tool.swift
//  SQLite3
//
//  Created by mac on 15/3/17.
//  Copyright (c) 2015年 gpr. All rights reserved.
//

import UIKit

/*
    三大核心方法
        - sqlite3_open
        - sqlite3_exec
        - sqlite3_prepare_v2
    
    使用 sqlite3 前要注意 :
        - 引入 sqlite3 动态库
        - 使用 bridge 把 sqlite3 框架桥接到 swift 中
*/

class SQLite3Tool: NSObject {
    private static let instance = SQLite3Tool()
    
    class var shareInstance: SQLite3Tool {
        return instance
    }
    
    /// db 对象
    var db: COpaquePointer = nil
    
    ///  打开数据库连接
    ///
    ///  :param: dbName 数据库文件名
    ///
    ///  :returns: 是否创建链接成功
    func openDataBase(dbName: String) -> Bool {
        let path = dbName.documentPath()
        println(path)
        if sqlite3_open(path.cStringUsingEncoding(NSUTF8StringEncoding)!, &db) == SQLITE_OK {
            println("创建数据库成功")
        } else {
            println("创建数据库失败")
        }
        return false
    }
    
    ///  执行一段 sql 语句
    ///
    ///  :param: sql sql语句
    ///
    ///  :returns: true 执行生效, false 执行失效
    func execSQL(sql: String) -> Bool {
        return sqlite3_exec(db, sql.cStringUsingEncoding(NSUTF8StringEncoding)!, nil, nil, nil) == SQLITE_OK
    }
}

// TODO: DML

// MARK: DQL
extension SQLite3Tool {
    
    ///  查询某一行 -> 返回字典
    func queryForRowDictResult(sql: String) -> [String: AnyObject]?{
        if let dictArray = execResultSetDictionary(sql) {
            return (dictArray[0] as [String: AnyObject])
        }
        return nil
    }
    
    ///  批量查询
    func execResultSetDictionary(sql: String) -> [[String: AnyObject]]?{
        var stmt: COpaquePointer = nil
        if sqlite3_prepare_v2(db, sql.cStringUsingEncoding(NSUTF8StringEncoding)!, -1, &stmt, nil) == SQLITE_OK {
            var recordList = [[String: AnyObject]]()
            while sqlite3_step(stmt) == SQLITE_ROW {
                recordList.append(rowDataWithStamentDictionary(stmt))
            }
            // 释放内存
            sqlite3_finalize(stmt)
            return recordList
        }
        return nil
    }

    ///  从结果集中取出该行数据
    private func rowDataWithStamentDictionary(stmt: COpaquePointer) -> [String: AnyObject] {
        var rowDict = [String: AnyObject]()
        for i in 0..<sqlite3_column_count(stmt) {
            let cName = sqlite3_column_name(stmt, Int32(i))
            let name = String.fromCString(cName)
            let col = Int32(i)
            switch sqlite3_column_type(stmt, col) {
            case SQLITE_FLOAT:
                rowDict[name!] = sqlite3_column_double(stmt, col)
            case SQLITE_INTEGER:
                rowDict[name!] = Int(sqlite3_column_int64(stmt, col))
            case SQLITE_TEXT:
                let cStr = UnsafePointer<CChar>(sqlite3_column_text(stmt, col))
                rowDict[name!] = String.fromCString(cStr)!
            case SQLITE_NULL:
                rowDict[name!] = NSNull()
            case let type:
                println("不支持的数据类型: \(type)")
            }
        }
        return rowDict
    }
    
    ///  查询某一行 -> 返回数组
    func queryForRowArrayResult(sql: String) -> [AnyObject]?{
        if let result = execResultSetArray(sql) {
            return (result[0] as! [AnyObject])
        }
        return nil
    }
    
    ///  批量查询
    func execResultSetArray(sql: String) -> [AnyObject]?{
        var stmt: COpaquePointer = nil
        if sqlite3_prepare_v2(db, sql.cStringUsingEncoding(NSUTF8StringEncoding)!, -1, &stmt, nil) == SQLITE_OK {
            var recordList = [AnyObject]()
            while sqlite3_step(stmt) == SQLITE_ROW {
                recordList.append(rowDataWithStament(stmt))
            }
            // 释放内存
            sqlite3_finalize(stmt)
            return recordList
        }
        return nil
    }
    
    ///  从结果集中取出该行数据
    private func rowDataWithStament(stmt: COpaquePointer) -> [AnyObject] {
        var columnList = [AnyObject]()
        for i in 0..<sqlite3_column_count(stmt) {
            let col = Int32(i)
            switch sqlite3_column_type(stmt, col) {
            case SQLITE_FLOAT:
                columnList.append(sqlite3_column_double(stmt, col))
            case SQLITE_INTEGER:
                columnList.append(Int(sqlite3_column_int64(stmt, col)))
            case SQLITE_TEXT:
                let cStr = UnsafePointer<CChar>(sqlite3_column_text(stmt, col))
                columnList.append(String.fromCString(cStr)!)
            case SQLITE_NULL:
                columnList.append(NSNull())
            case let type:
                println("不支持的数据类型: \(type)")
            }
        }
        return columnList
    }
}

// MARK: DDL
extension SQLite3Tool {

    // TODO: createTable
    
    ///  删除一张表格
    ///
    ///  :param: tableName 表名
    ///
    ///  :returns: true 删除成功, false 删除失败
    func dropTable(tableName: String) -> Bool {
        let sql = "DROP TABLE \(tableName);"
        return execSQL(sql)
    }
    
    ///  修改表格信息
    func alterTable(tableName: String, newTableName: String) -> Bool{
        let sql = "ALTER TABLE \(tableName) RENAME TO \(newTableName);"
        return execSQL(sql)
    }
}

// MARK: 事物相关的
extension SQLite3Tool {
    
    ///  开启事物
    func benginTransaction() {
        execSQL("BEGIN TRANSACTION")
    }
    
    ///  提交事物
    func commitTransaction() {
        execSQL("COMMIT TRANSACTION")
    }
    
    ///  回滚事物
    func rollBack() {
        execSQL("ROLLBACK TRANSACTION")
    }
}
